﻿using System;
using System.Data;
using System.Data.Common;

namespace HYElectricCommon
{
	public class Database
	{
		public static int ExecuteCommand(string commandStr)
		{
			int result;
			try
			{
				using (DbCommand dbCommand = Database.dbCon.CreateCommand())
				{
					dbCommand.CommandText = commandStr;
					dbCommand.CommandType = CommandType.Text;
					result = dbCommand.ExecuteNonQuery();
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return result;
		}

		public static object ExecuteScalar(string commandStr)
		{
			object result;
			try
			{
				using (DbCommand dbCommand = Database.dbCon.CreateCommand())
				{
					dbCommand.CommandText = commandStr;
					dbCommand.CommandType = CommandType.Text;
					dbCommand.ExecuteScalar();
					result = dbCommand.ExecuteScalar();
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return result;
		}

		public int ExecuteCommand(string commandStr, DbTransaction tr)
		{
			int result;
			try
			{
				using (DbCommand dbCommand = Database.dbCon.CreateCommand())
				{
					dbCommand.Transaction = tr;
					dbCommand.CommandText = commandStr;
					dbCommand.CommandType = CommandType.Text;
					result = dbCommand.ExecuteNonQuery();
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			return result;
		}

		public static DataTable ExecQuery(string sqlStr)
		{
			DataTable result;
			try
			{
				DataTable dataTable = new DataTable();
				Database.dbCon.Close();
				Database.dbCon.Open();
				using (DbCommand dbCommand = Database.dbCon.CreateCommand())
				{
					dbCommand.CommandText = sqlStr;
					dbCommand.CommandType = CommandType.Text;
					DbDataReader dbDataReader = dbCommand.ExecuteReader();
					dataTable = Database.DataReaderToDataTable(dbDataReader);
					((IDataReader)dbDataReader).Close();
					((IDisposable)dbDataReader).Dispose();
					result = dataTable;
				}
			}
			catch
			{
				result = null;
			}
			return result;
		}

		private static DataTable DataReaderToDataTable(IDataReader dataReader)
		{
			DataTable dataTable = new DataTable();
			DataTable schemaTable = dataReader.GetSchemaTable();
			DataTable result;
			try
			{
				for (int i = 1; i <= schemaTable.Rows.Count; i++)
				{
					dataTable.Columns.Add(schemaTable.Rows[i - 1]["ColumnName"].ToString().Trim(), Type.GetType(schemaTable.Rows[i - 1]["DataType"].ToString()));
				}
				while (dataReader.Read())
				{
					DataRow dataRow = dataTable.NewRow();
					for (int j = 0; j < schemaTable.Rows.Count; j++)
					{
						dataRow[j] = dataReader[j];
					}
					dataTable.Rows.Add(dataRow);
				}
				result = dataTable;
			}
			catch (Exception ex)
			{
				result = dataTable;
			}
			return result;
		}

		public DataTable ExecQuery(string sqlStr, DbTransaction tr)
		{
			DataTable result;
			try
			{
				new DataTable();
				DbCommand dbCommand = Database.dbCon.CreateCommand();
				dbCommand.Transaction = tr;
				dbCommand.CommandText = sqlStr;
				dbCommand.CommandType = CommandType.Text;
				result = Database.DataReaderToDataTable(dbCommand.ExecuteReader());
			}
			catch
			{
				result = null;
			}
			return result;
		}

		~Database()
		{
			if (Database.dbCon != null)
			{
				try
				{
					Database.dbCon.Dispose();
				}
				catch
				{
				}
			}
		}

		public static DbConnection dbCon;
	}
}
